<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Sponsorship_Model extends CI_Model {
	
	public function get_sponsor_info($username) {
		$query = "SELECT ss.* FROM smb_sponsors AS ss JOIN smb_sponsor_users AS ssu
			ON ss.id = ssu.id WHERE ssu.username = ?";
		$result = $this->db->query($query, array($username));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_sponsor_user($id) {
		$query = "SELECT su.* FROM smb_sponsor_users AS ssu JOIN smb_users AS su ON ssu.username = su.username
			WHERE ssu.id = ?";
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function edit_sponsor_info($id, $name, $address, $contactName, $contactNo) {
		$query = "UPDATE smb_sponsors SET name = ?, address = ?, contact_name = ?, contact_no = ?
			WHERE id = ?";
		
		$this->db->trans_start();
		$this->db->query($query, array($name, $address, $contactName, $contactNo, $id));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function add_sponsor($name, $address, $contactName, $contactNo, $user, $username, $password) {
		$query = "INSERT INTO smb_sponsors (id, name, address, contact_name, contact_no)
			VALUES (UUID(), ?, ?, ?, ?)";
		$userquery = "INSERT INTO smb_users (name, username, password, role)
			VALUES (?, ?, ?, ?)";
		$idquery = "SELECT * FROM smb_sponsors WHERE name = ? AND address = ?";
		$matchquery = "INSERT INTO smb_sponsor_users (id, username) VALUES (?, ?)";
		$flag = TRUE;
		
		$this->db->trans_begin();
		$this->db->query($query, array($name, $address, $contactName, $contactNo));
		$result = $this->db->query($idquery, array($name, $address));
		if ($result->num_rows() == 1) {
			$id = $result->row()->id;
			$this->db->query($userquery, array($user, $username, $password, 'sponsor'));
			$this->db->query($matchquery, array($id, $username));
		} else {
			$flag = FALSE;
		}
		
		if ($this->db->trans_status() === FALSE || $flag == FALSE) {
			$this->db->trans_rollback();
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			$this->db->trans_commit();
			return TRUE;
		}
	}
	
	public function get_sponsor_info_by_id($id) {
		$query = "SELECT * FROM smb_sponsors WHERE id = ?";
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			return NULL;
		}
	}
	
	public function get_sponsors($name) {
		$name = '%'.$name.'%';
		$query = "SELECT * FROM smb_sponsors WHERE name LIKE ?";
		$result = $this->db->query($query, array($name));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			return NULL;
		}
	}
	
	public function change_password($username, $old, $new) {
		$match = "SELECT * FROM smb_users WHERE username = ? AND password = ?";
		$result = $this->db->query($match, array($username, $old));
		if ($result->num_rows() == 1) {
			$query = "UPDATE smb_users SET password = ? WHERE username = ?";
			$result = $this->db->query($query, array($new, $username));
			if ($this->db->affected_rows() == 1) {
				return TRUE;
			} else {
				return FALSE;
			}
		} else {
			return FALSE;
		}
	}
}